﻿Imports Entidad
Imports System.Data
Imports System.Data.SqlClient

Public Class PickeadorDatos

    Function listarPickeadores() As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT u.USU_Codigo AS Codigo, " &
                              "u.USU_Descripcion AS Nombre, '    ' + " &
                              "CAST(ISNULL(SUM(CASE WHEN ISNULL(dadp.DADP_FechaFin,'') <> '' " &
                              "AND dadp.DADP_CantPick = dadp.DADP_Cantidad " &
                              "THEN 1 ELSE 0 END),0) AS VARCHAR(10)) " &
                              "+ ' / '+ CAST(COUNT(dadp.DCA_Codigo) AS VARCHAR(10))AS 'CantItem' " &
                              "FROM Usuario u " &
                              "LEFT JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                              "ON dadp.cia = u.cia AND dadp.SUC_Codigo = u.SUC_Codigo " &
                              "AND dadp.PICK_Codigo = u.USU_Codigo AND dadp.DADP_Activo = '1' AND dadp.TDOC_Codigo = 'PK' " &
                              "WHERE u.USU_Codigo NOT IN ('0099','200024') AND USU_Picking = '1' " &
                              "GROUP BY u.USU_Codigo, u.USU_Descripcion " &
                              "ORDER BY cantItem ASC"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)
        coneccion.Close()

        Return ds
    End Function

End Class
